# -*- coding: UTF-8 -*-
import datetime
import json
import math
import os
import sys
import time

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from itertools import groupby
from operator import itemgetter
from tqdm import tqdm
from src.db import AbsSqlServer
from src.db.AbsSqlServer import AbsSqlServerDev
from src.tasks.regionSpread.CustomJsonEncoder import toJson
from src.utils import BatchUtils, CommonUtils
from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.ProdDb import ProdDb
from src.utils.LogUtils import Logger
from src.db.FCDB import FCDB

logger = Logger()


class RegionSpread:
    def __init__(self, logger, localDb, dataCenterDb, absSqlServer):
        self.logger = logger
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.arg = sys.argv
        self.fcdb = FCDB(logSql=False, autocommit=True)
        self.absSqlServer = absSqlServer
        # self.cursor = AbsSqlServer.Conn.conn.cursor()
        print()

    def queryRegions(self):
        logger.info("读取全部区域列表")
        sql = """
                       SELECT * from regions 
                    where release_status =1 and release_status =1 and ver=0
        """
        query = self.localDb.Query(sql)
        dataMap = {

        }
        for item in query:
            dataMap.update({
                item['code']: item
            })
        logger.info("读取全部区域列表结束")
        return dataMap

    # 获取所有债券数据
    def getAllEntList(self, entType):
        logger.info("读取城投债券列表")
        sql = """
                SELECT
                    ee.credit_code,
                    ee.platform_region_code,
                    ee.qe_eval,
                    ee.nature  
                from
                     ent_enterprise ee
                    where ee.ent_type =%s
        """
        return self.localDb.Query(sql, entType)

    def getBondsInfo(self, updateTime, entType):
        sql = """
            SELECT
                    ee.credit_code,
                    dd.secode,
                    dd.security_id,
                    dd.new_rate,
                    dd.is_guarantee,
                    dd.qe_eval 
                from
                     ent_enterprise ee
                     left join deb_debenture_base dd on ee.credit_code =dd.issuer_code 
                    where ee.ent_type =%s and (dd.new_rate is not null or dd.is_guarantee is not null)
        """
        return self.localDb.Query(sql, entType)

    # 产业
    def updateindustryEntBasicInfo(self):
        # 获取全部债券
        entList = self.getAllEntList("T001")
        # 查询全部区域
        regionsAll = dict(self.queryRegions())
        # 根据债券全称分组，判断exchange
        # 排序
        for item in entList:
            credit_code = item['credit_code']
            qe_eval = item['qe_eval']
            platform_rank_search = None
            if qe_eval is not None:
                platform_rank_search = str(qe_eval).replace('+', '').replace('-', '')
            dbResult = {
                "credit_code": credit_code,
                "qe_eval": qe_eval,
                "qe_eval_search": platform_rank_search,
                "nature": item['nature'],
            }
            self.updateEntData(dbResult)

    def updateEntBasicInfo(self):
        # 获取全部债券
        entList = self.getAllEntList("T002")
        # 查询全部区域
        regionsAll = dict(self.queryRegions())
        # 根据债券全称分组，判断exchange
        # 排序
        for item in entList:
            platform_region_code = item['platform_region_code']
            credit_code = item['credit_code']
            qe_eval = item['qe_eval']
            platform_rank_search = None
            if qe_eval is not None:
                platform_rank_search = str(qe_eval).replace('+', '').replace('-', '')

            level = regionsAll.get(platform_region_code)['level']
            if level > 4:
                level = 4
            dbResult = {
                "platform_region_code": platform_region_code,
                "province_code": regionsAll.get(platform_region_code)['province_code'],
                "city_code": regionsAll.get(platform_region_code)['city_code'],
                "county_code": regionsAll.get(platform_region_code)['county_code'],
                "functional_code": regionsAll.get(platform_region_code)['functional_code'],
                "type": regionsAll.get(platform_region_code)['type'],
                "level": level,
                "credit_code": credit_code,
                "p_functional_code": regionsAll.get(platform_region_code)['p_functional_code'],
                "qe_eval": qe_eval,
                "platform_rank_search": platform_rank_search
            }
            self.updateData(dbResult)

    def querySyncDataCofig(self, syncType):
        typeName = None
        if syncType == 1:
            typeName = "irs_platform_ent_info_time"
        if syncType == 2:
            typeName = "irs_platform_bond_info_time"
        if syncType == 3:
            typeName = "irs_industry_bond_info_time"
        sql = "SELECT " + typeName + " FROM regular_income_v2.sync_data_config limit 1"
        return self.localDb.QueryOne(sql)

    def updateIndustryBondBasicInfo(self):
        updateTime = self.querySyncDataCofig(3)
        info = self.getBondsInfo(updateTime, "T001")
        for item in info:
            secode = item['secode']
            logger.info("同步债券SECODE：" + secode + "债券基础信息到利差库")
            dbResult = {
                "secode": secode,
                "new_rate": item['new_rate'],
                "is_guarantee": item['is_guarantee'],
                "qe_eval": item['qe_eval']
            }
            self.updateDataIndustryBondsBasticInfo(dbResult, "Fits2Test.dbo.base_bonds_interest_rate_spread_industry")

    # type=2
    # 同步城投债券- 是否担保  债项评级
    def updateBondBasicInfo(self):
        updateTime = self.querySyncDataCofig(2)
        info = self.getBondsInfo(updateTime, "T002")
        for item in info:
            secode = item['secode']
            logger.info("同步债券SECODE：" + secode + "债券基础信息到利差库")
            dbResult = {
                "secode": secode,
                "new_rate": item['new_rate'],
                "is_guarantee": item['is_guarantee'],
            }
            self.updateDataBondsBasticInfo(dbResult, "Fits2Test.dbo.base_bonds_interest_rate_spread_platform")

    def process(self):
        typeByCommand = CommonUtils.isCommandParam(self.arg, "type")
        if typeByCommand['exist']:
            typeByCommand = typeByCommand["value"]
            if typeByCommand == "1":
                # 同步企业基础数据-区域债券单券利差表
                self.updateEntBasicInfo()
            if typeByCommand == "2":
                # 同步债券基础数据-区域债券单券利差表
                logger.info("同步债券基础数据->区域债券单券利差表开始")
                self.updateBondBasicInfo()
                logger.info("同步债券基础数据->区域债券单券利差表完成")
            if typeByCommand == "3":
                logger.info("同步利差表企业基础数据->产业债券单券利差表开始")
                self.updateindustryEntBasicInfo()
                logger.info("同步利差表企业基础数据->产业债券单券利差表完成")
            if typeByCommand == "4":
                # 同步债券基础数据-区域债券单券利差表
                logger.info("同步债券基础数据->产业债券单券利差表开始")
                self.updateIndustryBondBasicInfo()
                logger.info("同步债券基础数据->产业债券单券利差表完成")
        else:
            logger.error("参数{type}不存在")
            exit()

    def updateDataBondsBasticInfo(self, dbResult, tableName):
        sql = "UPDATE " + tableName + " SET new_rate=%s, is_guarantee=%s WHERE secode=%s"
        secode = dbResult['secode']
        if secode is None:
            return
        self.absSqlServer.execute_non_query(sql,
                                            (dbResult['new_rate'], dbResult['is_guarantee'], secode))
        logger.info("更新数据" + secode + "完成")

    def updateDataIndustryBondsBasticInfo(self, dbResult, tableName):
        sql = "UPDATE " + tableName + " SET new_rate=%s, is_guarantee=%s WHERE secode=%s"
        secode = dbResult['secode']
        if secode is None:
            return
        self.absSqlServer.execute_non_query(sql,
                                            (dbResult['new_rate'], dbResult['is_guarantee'], secode))
        logger.info("更新数据" + secode + "完成")

    def updateEntData(self, dbResult):
        sql = """
            UPDATE Fits2Test.dbo.base_bonds_interest_rate_spread_industry
            SET rating_rank=%s,rating_rank_search=%s,nature=%s
             WHERE credit_code=%s;
        """
        credit_code = dbResult['credit_code']
        if credit_code is None:
            return
        qe_eval = dbResult['qe_eval']
        # formatSql = sql % (dbResult['platform_region_code'], dbResult['province_code'],
        #                    dbResult['city_code'], dbResult['county_code'], dbResult['type'],
        #                    dbResult['level'], dbResult['functional_code'], dbResult['p_functional_code'], credit_code)
        self.absSqlServer.execute_non_query(sql, (qe_eval,
                                                  dbResult['qe_eval_search'], dbResult['nature'], credit_code))
        logger.info("更新数据" + credit_code + "完成")

    def updateData(self, dbResult):
        sql = """
            UPDATE Fits2Test.dbo.base_bonds_interest_rate_spread_platform
            SET platform_region_code=%s, province_code=%s, city_code=%s, county_code=%s, [type]=%s, [level]=%s, functional_code=%s,p_functional_code=%s,
            platform_rank=%s,platform_rank_search=%s
             WHERE credit_code=%s;
        """
        credit_code = dbResult['credit_code']
        if credit_code is None:
            return
        # formatSql = sql % (dbResult['platform_region_code'], dbResult['province_code'],
        #                    dbResult['city_code'], dbResult['county_code'], dbResult['type'],
        #                    dbResult['level'], dbResult['functional_code'], dbResult['p_functional_code'], credit_code)
        self.absSqlServer.execute_non_query(sql, (dbResult['platform_region_code'], dbResult['province_code'],
                                                  dbResult['city_code'], dbResult['county_code'], dbResult['type'],
                                                  dbResult['level'], dbResult['functional_code'],
                                                  dbResult['p_functional_code'], dbResult['qe_eval'],
                                                  dbResult['platform_rank_search'], credit_code))
        logger.info("更新数据" + credit_code + "完成")


if __name__ == "__main__":
    logger = Logger()
    localDb = ProdDb(logSql=False, autocommit=True)
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    absSqlServer = AbsSqlServerDev()
    t = time.perf_counter()
    logger.info("========计算区域利差数据 开始 ========")
    task = RegionSpread(logger=logger, localDb=localDb, dataCenterDb=dataCenterDb, absSqlServer=absSqlServer)
    task.process()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('总耗时: %d分%.3f秒' % (m, s))
    logger.info("========计算区域利差数据 结束 ========")
